using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using GameShow.DAL.DTO;
using NLog;

namespace GameShow.DAL
{
    public class QuestionDAL : DALBase
    {

        private static Logger log = LogManager.GetCurrentClassLogger();


        public DataSet InsertQuestionPackage()
        {
            DataSet ds = new DataSet("TT");
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "SELECT * FROM dbo.tblQuestions";
                command.Connection = AppConnection;
                adapter.SelectCommand = command;
                adapter.Fill(ds, "ITC");

            }
            catch (Exception ex)
            {
                log.ErrorException(string.Empty, ex);
            }
            finally
            {
                AppConnection.Close();
            }
            return ds;
        }

        public DataSet GetQuestionPackage()
        {
            DataSet dataSet = new DataSet("TT");
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "SELECT PackageID, PackageName FROM dbo.tblQuestionPackages";
                command.Connection = AppConnection;
                adapter.SelectCommand = command;
                adapter.Fill(dataSet, "ITC");

            }
            catch (Exception ex)
            {
                log.ErrorException(string.Empty, ex);
            }
            finally
            {
                AppConnection.Close();
            }
            return dataSet;
        }

        public DataSet ShowContent(int iPackage, int iType)
        {
            DataSet dataSet = new DataSet("TT");
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "SELECT QuestionContent FROM tblQuestions WHERE PackageId = " + iPackage + " AND QuestionType =" + iType;
                command.Connection = AppConnection;
                adapter.SelectCommand = command;
                adapter.Fill(dataSet, "ITC");

            }
            catch (Exception ex)
            {
            }
            finally
            {
                AppConnection.Close();
            }
            return dataSet;
        }

        public bool DeleteQuestionErr(int iQuestion)
        {
            bool iResual = false;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "DELETE FROM tblQuestions WHERE QuestionId = " + iQuestion;
                command.Connection = AppConnection;
                command.ExecuteNonQuery();
                iResual = true;
            }
            catch (Exception ex)
            {
                log.ErrorException(string.Empty, ex);
                iResual = false;
            }
            finally
            {
                AppConnection.Close();
            }
            return iResual;
        }

        public bool DeleteAnsError(int iQuestionID)
        {
            bool iResual = false;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "DELETE FROM tblAnswers WHERE questionid = " + iQuestionID;
                command.Connection = AppConnection;
                command.ExecuteNonQuery();
                iResual = true;
            }
            catch (Exception ex)
            {
                log.ErrorException(string.Empty, ex);
                iResual = false;
            }
            finally
            {
                AppConnection.Close();
            }
            return iResual;
        }

        public bool InsertQuestion(int iPackageID, string strContent, int iTypeQuest)
        {
            bool iResual = false;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "INSERT INTO dbo.tblQuestions VALUES (@iPackageID, @strContent,@iTypeQuest)";
                command.Parameters.Add(new SqlParameter("iPackageID", iPackageID));
                command.Parameters.Add(new SqlParameter("strContent", strContent));
                command.Parameters.Add(new SqlParameter("iTypeQuest", iTypeQuest));
                command.Connection = AppConnection;
                command.ExecuteNonQuery();
                iResual = true;
            }
            catch (Exception ex)
            {
                log.ErrorException(string.Empty, ex);
            }
            finally
            {
                AppConnection.Close();
            }
            return iResual;
        }

        public bool InsertAnswer(int iQuestionId, string strAnsign, string strAnsContent, bool iValues)
        {
            bool iResual = false;
            SqlCommand command = new SqlCommand();
            try
            {

                command.CommandText = "INSERT INTO tblAnswers VALUES (@iQuestionId,@strAnsign,@strAnsContent,@Val)";
                command.Parameters.Add(new SqlParameter("iQuestionId", iQuestionId));
                command.Parameters.Add(new SqlParameter("strAnsign", strAnsign));
                command.Parameters.Add(new SqlParameter("strAnsContent", AppSecurity.Base64Encode(strAnsContent.Replace("'", "''"))));
                command.Parameters.Add(new SqlParameter("Val", Convert.ToInt32(iValues)));


                command.Connection = AppConnection;
                command.ExecuteNonQuery();
                iResual = true;
            }
            catch (Exception ex)
            {
                log.ErrorException(string.Empty, ex);
            }
            finally
            {
                AppConnection.Close();
            }
            return iResual;
        }

        public bool CheckQuestionTypeOfPacket(int iPackage, int iType)
        {
            bool iResual = false;
            DataSet dataSet = new DataSet("TT");
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "select * from dbo.tblQuestions where packageID =" + iPackage + "and QuestionType =" + iType;
                command.Connection = AppConnection;
                adapter.SelectCommand = command;
                adapter.Fill(dataSet, "ITC");
                if (dataSet.Tables[0].Rows.Count >= 1)
                    iResual = true;
            }
            catch (Exception ex)
            {
            }
            finally
            {
                AppConnection.Close();
            }
            return iResual;
        }

        public bool CheckNumberQuestionOfPackage(int iPackage)
        {
            bool iResual = false;
            DataSet dataSet = new DataSet("TT");
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "select * from dbo.tblQuestions where Packageid =" + iPackage;
                command.Connection = AppConnection;
                adapter.SelectCommand = command;
                adapter.Fill(dataSet, "ITC");
                if (dataSet.Tables[0].Rows.Count < 2)
                    iResual = true;
            }
            catch (Exception ex)
            {
            }
            finally
            {
                AppConnection.Close();
            }
            return iResual;
        }

        public bool InsertPackage(int iPackageId, string strPackNeme, int teamID)
        {
            bool iResual = false;
            try
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "INSERT INTO dbo.tblQuestionPackages VALUES (" + iPackageId + ",N'" + strPackNeme + "'," + teamID + ")";
                command.Connection = AppConnection;
                command.ExecuteNonQuery();
                iResual = true;
            }
            catch (Exception ex)
            {
                log.ErrorException(string.Empty, ex);
            }
            finally
            {
                AppConnection.Close();
            }
            return iResual;
        }

        public DataSet ShowAnswer(int iQuestion)
        {
            DataSet dataSet = new DataSet("TT");
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "SELECT * FROM tblAnswers ta WHERE ta.QuestionId =" + iQuestion;
                command.Connection = AppConnection;
                adapter.SelectCommand = command;
                adapter.Fill(dataSet, "ITC");

            }
            catch (Exception ex)
            {
            }
            finally
            {
                AppConnection.Close();
            }
            return dataSet;
        }

        public int GetQuestionId(int iPackageId, int iTypeQuestion)
        {
            try
            {
                SqlCommand cmd = new SqlCommand("SELECT tq.QuestionId FROM tblQuestions tq WHERE tq.PackageId =" + iPackageId.ToString() + " AND tq.QuestionType = " + iTypeQuestion.ToString(), AppConnection);
                return int.Parse(cmd.ExecuteScalar().ToString());
            }
            catch (Exception ex)
            {
                log.ErrorException(string.Empty, ex);
            }
            finally
            {
                AppConnection.Close();
            }

            return 0;
        }

        public DataTable GetQuestionItem(int iPackageId, int iQuestionType)
        {
            DataTable result = new DataTable();
            try
            {
                SqlCommand cmd = new SqlCommand("SELECT tq.QuestionId, tq.PackageId, tq.QuestionContent, tq.QuestionType FROM tblQuestions tq WHERE tq.PackageId =" + iPackageId.ToString() + " AND tq.QuestionType = " + iQuestionType.ToString(), AppConnection);
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                adp.Fill(result);
                return result;
            }
            catch (Exception ex)
            {
                log.ErrorException(string.Empty, ex);
            }
            finally
            {
                AppConnection.Close();
            }

            return null;
        }

        //CuongBM: 12-May-2012
        //
        public Question GetQuestion(int questionId)
        {
            //Get Answer
            DataSet dataSet = new DataSet("TT");
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "SELECT q.QuestionId, q.PackageId, QuestionContent, QuestionType, AnsID, AnsSign, AnsContent, Value " +
                                      "FROM tblQuestions q, tblAnswers a " +
                                      "WHERE q.QuestionId = a.QuestionId " +
                                      "  AND q.QuestionId = " + questionId;

                command.Connection = AppConnection;
                adapter.SelectCommand = command;
                adapter.Fill(dataSet, "ITC");

                Question q = new Question();

                q.Id = Int32.Parse(dataSet.Tables[0].Rows[0][0].ToString());
                q.PackageId = Int32.Parse(dataSet.Tables[0].Rows[0][1].ToString());
                q.QuestionContent = dataSet.Tables[0].Rows[0][2].ToString();
                q.QuestionType = Int32.Parse(dataSet.Tables[0].Rows[0][3].ToString());


                //Danh sach cau tra loi
                foreach (DataRow dr in dataSet.Tables[0].Rows)
                {
                    Answer a = new Answer();

                    a.QuestionId = Int32.Parse(dr[0].ToString());
                    a.AnsId = Int32.Parse(dr[4].ToString());
                    a.AnsSign = dr[5].ToString();

                    //CuongBM: Giai ma cau tra loi
                    //a.AnsContent = dr[6].ToString();
                    a.AnsContent = AppSecurity.Base64Decode(dr[6].ToString());

                    a.Value = bool.Parse(dr[7].ToString());

                    q.answerList.Add(a);
                }

                return q;
            }

            catch (Exception ex)
            {
                log.ErrorException(string.Empty, ex);
            }
            finally
            {
                AppConnection.Close();
            }

            return null;
        }

        public Question GetQuestion(int packId, int questionType)
        {
            //Get Answer
            DataSet dataSet = new DataSet("TT");
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "SELECT q.QuestionId, q.PackageId, QuestionContent, QuestionType, AnsID, AnsSign, AnsContent, Value " +
                                      "FROM tblQuestions q, tblAnswers a " +
                                      "WHERE q.QuestionId = a.QuestionId " +
                                      "  AND q.PackageId = " + packId + " AND QuestionType = " + questionType;

                command.Connection = AppConnection;
                adapter.SelectCommand = command;
                adapter.Fill(dataSet, "ITC");

                Question q = new Question();

                q.Id = Int32.Parse(dataSet.Tables[0].Rows[0][0].ToString());
                q.PackageId = Int32.Parse(dataSet.Tables[0].Rows[0][1].ToString());
                q.QuestionContent = dataSet.Tables[0].Rows[0][2].ToString();
                q.QuestionType = Int32.Parse(dataSet.Tables[0].Rows[0][3].ToString());


                //Danh sach cau tra loi
                foreach (DataRow dr in dataSet.Tables[0].Rows)
                {
                    Answer a = new Answer();

                    a.QuestionId = Int32.Parse(dr[0].ToString());
                    a.AnsId = Int32.Parse(dr[4].ToString());
                    a.AnsSign = dr[5].ToString();

                    //CuongBM: Giai ma cau tra loi
                    //a.AnsContent = dr[6].ToString();
                    a.AnsContent = AppSecurity.Base64Decode(dr[6].ToString());

                    a.Value = bool.Parse(dr[7].ToString());

                    q.answerList.Add(a);
                }

                return q;
            }

            catch (Exception ex)
            {
                log.ErrorException(string.Empty, ex);
            }
            finally
            {
                AppConnection.Close();
            }

            return null;
        }

        public DataTable GetFreePackages()
        {
            DataTable result = new DataTable();
            try
            {
                SqlCommand cmd = new SqlCommand("SELECT * FROM tblQuestionPackages WHERE TeamId = 0", AppConnection);
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                adp.Fill(result);
                return result;
            }
            catch (Exception ex)
            {
                log.ErrorException(string.Empty, ex);
            }
            finally
            {
                AppConnection.Close();
            }

            return null;
        }

        public bool SaveQuestion(int iQuestionID, int iPackageId, string strContent, int iTypeQuestion)
        {
            if (strContent.Trim().Length > 0)
            {
                strContent = strContent[0].ToString().ToUpper() + strContent.Substring(1, strContent.Length - 1);
            }

            bool iResual = false;
            SqlCommand command = new SqlCommand();
            try
            {
                if (iQuestionID > 0) // Sửa thông tin câu hỏi
                {
                    command.CommandText = "UPDATE dbo.tblQuestions SET QuestionContent = N'" + strContent.Replace("'", "''") +
                                                                "', PackageID= " + iPackageId +
                                                                ", QuestionType = " + iTypeQuestion +
                                                " WHERE QuestionID = " + iQuestionID;
                }
                else// Thêm mới câu hỏi
                {
                    command.CommandText = "INSERT INTO dbo.tblQuestions VALUES (" + iPackageId + ",N'"
                                                                                + strContent.Replace("'", "''") + "',"
                                                                                + iTypeQuestion + ")";
                }
                command.Connection = AppConnection;
                command.ExecuteNonQuery();
                iResual = true;
            }
            catch (Exception ex)
            {
                log.ErrorException(string.Empty, ex);
            }
            finally
            {
                AppConnection.Close();
            }
            return iResual;
        }

        public bool SaveAnswer(int iAnswerId, int iQuestionId, string strAnsign, string strAnsContent, bool chkAnsTrue)
        {
            bool iResual = false;
            SqlCommand command = new SqlCommand();
            try
            {
                if (iAnswerId > 0) // Sửa thông tin đáp án
                {
                    command.CommandText = "UPDATE dbo.tblAnswers SET AnsContent = N'" + AppSecurity.Base64Encode(strAnsContent.Replace("'", "''")) +
                                                                "', AnsSign= N'" + strAnsign +
                                                                "', QuestionId= " + iQuestionId +
                                                                ", Value = " + Convert.ToInt32(chkAnsTrue) +
                                                " WHERE AnsId = " + iAnswerId;
                }
                else// Thêm mới đáp án
                {
                    command.CommandText = "INSERT INTO dbo.tblAnswers(QuestionId, AnsContent, AnsSign, Value ) VALUES (" + iQuestionId + ",N'"
                                                                                + AppSecurity.Base64Encode(strAnsContent.Replace("'", "''")) + "',N'"
                                                                                + strAnsign + "',"
                                                                                + Convert.ToInt32(chkAnsTrue) + ")";
                }
                command.Connection = AppConnection;
                command.ExecuteNonQuery();
                iResual = true;
            }
            catch (Exception ex)
            {
                log.ErrorException(string.Empty, ex);
            }
            finally
            {
                AppConnection.Close();
            }
            return iResual;
        }

        public DataTable GetAnswerByQuesId(int iQuestionId)
        {
            DataTable result = new DataTable();
            try
            {
                SqlCommand cmd = new SqlCommand("SELECT ta.AnsId, ta.QuestionId, ta.AnsSign, ta.AnsContent, ta.Value FROM tblAnswers ta WHERE ta.QuestionId = " + iQuestionId.ToString() + " ORDER BY ta.AnsSign", AppConnection);
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                adp.Fill(result);
                return result;
            }
            catch (Exception ex)
            {
                log.ErrorException(string.Empty, ex);
            }
            finally
            {
                AppConnection.Close();
            }

            return null;
        }

        public DataTable GetByQuestionPackId(int iQuestionPackId)
        {
            DataTable result = new DataTable();
            try
            {
                string strQuery = "SELECT a.PackageName ,b.QuestionContent ," +
                        "CASE b.QuestionType WHEN 0 THEN N'Tín dụng' WHEN 1 THEN N'Kế toán' " +
                        "END AS [Loại câu hỏi] FROM tblQuestionPackages A, tblQuestions B WHERE A.PackageId = B.PackageId AND A.PackageId = " + iQuestionPackId.ToString();
                SqlCommand cmd = new SqlCommand(strQuery, AppConnection);
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                adp.Fill(result);
                return result;
            }
            catch (Exception ex)
            {
                DisplayMessage.ShowError(ex.Message);
            }
            finally
            {
                AppConnection.Close();
            }

            return null;
        }
    }
}
